﻿--跟进人员的成单
CREATE PROCEDURE [dbo].[proc_Order_Getlist_GenJin_Kf1]
	(
		@UserName nvarchar(50),
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	;WITH List1 As(
		Select OrderId,ProjectId,ProductName,IsFinish,ExecPerson,CreateDate,IsHedge
		From Project
		Where CreateDate>=@sRq And CreateDate<=@eRq And ProductId In(select id from Product where flag=1) And OrderId In(
			Select OId From [Order] Where GenJinKfAccount=@UserName
			And (@Stext='' Or CusName like '%'+@Stext+'%')
		)
	),
	List As(
		Select ROW_NUMBER() OVER (Order By b.IsFinish, b.CreateDate Desc) As Row,Id
		,OId
		,a.OrderId
		,CusId
		,CusName
		,TypeName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,b.CreateDate
		,YingShouPrice
		,ShiShouPrice
		,IsGreen
		,StateId As StateId1
		,IsNull(TrackKfAccount,'') As TrackKfAccount	--维护客服
		,b.ProjectId,b.ProductName,b.IsFinish,b.ExecPerson
		,CompanyId
		,ExCompanyId
		,b.IsHedge
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order] a,List1 b Where a.OId = b.OrderId
	)
	
	Select *,IsFinish As StateId,
		IsNull((Select Title From Order_State Where Id=List.StateId1),'') As StateName,
		IsNull((Select Top 1 EName From Employee Where UserName=List.ExecPerson),'') As UserName,	--该网站项目的接口人
		(Select Count(0) From List) As RecordCount
	From List
	Where Row Between @StartIndex and @EndIndex
	Order By Row
End
